package com.oceansoft.mobile.biz.admin.service;

import com.alibaba.fastjson.JSONObject;
import com.oceansoft.mobile.biz.admin.entity.Count;
import com.oceansoft.mobile.common.Constant;
import com.oceansoft.mobile.common.base.entity.Pager;
import com.oceansoft.mobile.common.base.entity.Query;
import com.oceansoft.mobile.common.base.entity.Result;
import com.oceansoft.mobile.common.dao.ICommonDao;
import com.oceansoft.mobile.weixin.entity.message.req.SuperMessage;
import com.oceansoft.mobile.weixin.util.DateUtil;

import freemarker.template.Configuration;
import freemarker.template.Template;

import io.netty.util.internal.StringUtil;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.File;
import java.io.Writer;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by BEN on 2016/4/12.
 */
@Service
public class CountAdminServiceImpl implements ICountAdminService {

    @Resource
    private ICommonDao commonDao;


    public Pager<SuperMessage> loadNewsByPage(String toUserName,Query query, String start, String end) throws Exception {
        String page_sql = "";
        String count_sql = "";
        List<SuperMessage> list = new ArrayList<SuperMessage>();
        int total = 0;

        page_sql += "SELECT T.*,T1.nickname FROM wx_msg T,wx_user T1 WHERE T.FromUserName = T1.openid and T.ToUserName =? ";
        if (start != null && !start.equals("")) {
            page_sql += " and from_unixtime(T.createtime/1000) >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            page_sql += " and from_unixtime(T.createtime/1000) <='" + end + "'";
        }
        page_sql += Query.getPageSQL("T", query);
        count_sql += "SELECT COUNT(*) FROM wx_msg T,wx_user T1 WHERE T.FromUserName = T1.openid and T.ToUserName = ? ";
        if (start != null && !start.equals("")) {
            count_sql += " and from_unixtime(T.createtime/1000) >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            count_sql += " and from_unixtime(T.createtime/1000) <='" + end + "'";
        }
        count_sql += Query.getCountSQL("T", query);
        list = commonDao.loadList(page_sql, SuperMessage.getFullNewsMapper(), toUserName);
        total = commonDao.getCount(count_sql, toUserName);

        return new Pager<SuperMessage>(total, query.getPage(), list);
    }


    public Pager findCountList(Query query, String start, String end) {
        String page_sql = "";
        String count_sql = "SELECT count(*) FROM t_url_mark t";
        
//        page_sql +="select d.name,d.viewcount,d.submitcount,c.sourceName as source from(";
        page_sql += " select r.id,r.name,a.viewcount,a.submitcount  from t_url_mark r left join ( ";
        page_sql += " select t.id,sum(t.viewcount) viewcount ,sum(t.submitcount)submitcount from t_url_tj t where 1=1";

        if (start != null && !start.equals("")) {
            page_sql += " and tjdate >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            page_sql += " and tjdate <='" + end + "'";
        }
        page_sql +=" GROUP BY t.id";
        page_sql += " ) a on r.id=a.id";
//        page_sql += " ) d ,t_url_tj_dict c where d.source=c.sourceid";
        query.setSort(query.getSort());
System.out.println(page_sql);
        List list = commonDao.loadMapList(page_sql);
        long total = commonDao.getCount(count_sql);

        return new Pager(total, query.getPage(), list);
    }


    public Pager findSumList(Query query, String start, String end) {

        int total = 0;
        String page_sql = "select '关注量' name,sum(new_user) viewcount from wx_user_cumulate a where 1=1 ";

        if (start != null && !start.equals("")) {
            page_sql += " and a.ref_date >= '" + start + "'";
        }

        if (end != null && !end.equals("")) {
            page_sql += " and a.ref_date<='" + end + "'";
        }
        page_sql += " union ";
        page_sql += " select '取消关注量' name,sum(cancel_user) viewcount from wx_user_cumulate b where 1=1";
        if (start != null && !start.equals("")) {
            page_sql += " and b.ref_date >= '" + start + "'";
        }

        if (end != null && !end.equals("")) {
            page_sql += " and b.ref_date<='" + end + "'";
        }
        page_sql += " union ";
        page_sql += " select '净增关注量' name,sum(new_user)-sum(cancel_user) viewcount from wx_user_cumulate c where 1=1";
        if (start != null && !start.equals("")) {
            page_sql += " and c.ref_date >= '" + start + "'";
        }

        if (end != null && !end.equals("")) {
            page_sql += " and c.ref_date<='" + end + "'";
        }
        page_sql += " union ";
        page_sql += " select '关注总量' name, max(cumulate_user) viewcount from wx_user_summary d where 1=1 and ref_date='"+DateUtil.date2String(DateUtil.getYesterdayDate(), DateUtil.PATTERN_DATE)+"' ";
//        if (start != null && !start.equals("")) {
//            page_sql += " and d.ref_date >= '" + start + "'";
//        }
//
//        if (end != null && !end.equals("")) {
//            page_sql += " and d.ref_date<='" + end + "'";
//        }
System.out.println(page_sql);
        List list = commonDao.loadMapList(page_sql);
        return new Pager(total, query.getPage(), list);
    }

    public void export(HttpServletRequest request, HttpServletResponse response,String doctype, String daocmode, Map<String, Object> nqxMap) {
        try {
            String _ftltmpPath = request.getSession().getServletContext()
                    .getRealPath("/");
            _ftltmpPath += Constant.PATH_FTL_TEMPLATE;
            response.setContentType("application/octet-stream;charset=UTF-8");
            request.setCharacterEncoding("UTF-8");
            Writer writer = null;
            String timeTemp = new SimpleDateFormat("yyyyMMddHHmmss")
                    .format(new Date()) + "."+doctype;
            String fileName = Constant.FILE_COMM + timeTemp;
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String(fileName.getBytes("GBK"), "ISO8859-1"));
            writer = response.getWriter();
            File _tmp = new File(_ftltmpPath);
            Configuration configuration = new Configuration();
            if (_tmp.exists()) {
                configuration.setDirectoryForTemplateLoading(_tmp);
                Template t = configuration.getTemplate(daocmode, "utf-8");
                t.process(nqxMap, writer);
                writer.flush();
                writer.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


	@Override
	public Pager findCountInfo(Query query, String start, String end, String id) {
        String page_sql = "select (select a.sourceName from t_url_tj_dict a where a.sourceid=source) source ,sum(viewcount) viewcount,sum(submitcount) submitcount from t_url_tj where id ="+id;
        if (start != null && !start.equals("")) {
            page_sql += " and tjdate >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            page_sql += " and tjdate <='" + end + "'";
        }
        page_sql+=" group by source";
        String count_sql="select count(1) from ("+page_sql+") b";
        long total = commonDao.getCount(count_sql);
        List list = commonDao.loadMapList(page_sql);
        return new Pager(total, query.getPage(), list);
    }


	/*@Override
	public Pager findCountShowInfo(Query query, String start, String end,
			String id, String source) {
		String secondName=findeSecondName(source);
		 String page_sql = "select (select a.sourceName from t_url_tj_dict a where a.sourceid=source) sourcee,sum(viewcount) viewcount from t_url_tj where id ='"+id+"' and (source='"+source+"' or source like '"+secondName+"%')";
	        if (start != null && !start.equals("")) {
	            page_sql += " and tjdate >='" + start + "'";
	        }
	        if (end != null && !end.equals("")) {
	            page_sql += " and tjdate <='" + end + "'";
	        }
	        page_sql+=" group by sourcee order by sourcee desc";
	        System.out.println(page_sql);
	        String count_sql="select count(1) from ("+page_sql+") b";
	        long total = commonDao.getCount(count_sql);
	        List list = commonDao.loadMapList(page_sql);
	        return new Pager(total, query.getPage(), list);
	}*/


	/**
	 * 方法内容已按照要求修改好
	 */
	@Override
	public Pager<Count> findCountShowIn(Query query, String start, String end,
                                        String id, String source, HttpServletRequest request) {
		String secondName=findeSecondName(source);
		// String page_sql = "select (select a.sourceName from t_url_tj_dict a where a.sourceid=source) sourcee,sum(viewcount) viewcount from t_url_tj where id ='"+id+"' and (source='"+source+"' or source like '"+secondName+"%')";
	     //统计的时间段
		 String sjSql = "";
		if (!StringUtil.isNullOrEmpty(start)) {
			sjSql += " and tjdate >='" + start + "'";
		}
		if (!StringUtil.isNullOrEmpty(end)) {
			sjSql += " and tjdate <='" + end + "'";
		}
		//特殊处理
		String spSql = "";
		if(!StringUtil.isNullOrEmpty(secondName)&&"ewm_320594".equals(secondName))
		{
			spSql = " or d.sourceid like 'ewm_320595%' ";
		}
		//统计的分局
		 String fjSql = "(d.sourceid like '"+secondName+"%' or sourceid = '"+source+"'  "+spSql+" )";
		 
		 
		 String  page_sql = "select d.sourceName sourcee,'1' isfirst,IFNULL(viewcount,0) viewcount,IFNULL(submitcount,0) submitcount ,d.sourceid from t_url_tj_dict d LEFT JOIN "
				 +" (  SELECT	source, sum(viewcount) viewcount,sum(submitcount) submitcount FROM 	t_url_tj "
			   +" WHERE	id = '"+id+"'  	"+sjSql+" GROUP BY 	source "
			+" 	 ) a on a.source=d.sourceid where  " + fjSql +" ORDER BY LENGTH(sourceid),sourceid";

		 long t1 = System.currentTimeMillis();
	        System.out.println(page_sql);
	        if(query.getPage()==1)
	        {
	        	List exportList=commonDao.loadMapList(page_sql);
	        	request.getSession().setAttribute("exportList",exportList);
	        }
	        String count_sql="select count(1) from ("+page_sql+") b";
             page_sql += Query.getPageSQL("T", query);
	        long total = commonDao.getCount(count_sql);
	        List list = commonDao.loadMapList(page_sql);
	        long t2 = System.currentTimeMillis();
	        double d =  (t2-t1)/1000.00;
	        System.out.println(d);
	        return new Pager<Count>(total, query.getPage(), list);
	}


	/*@Override
	public Pager<Count> findCountIn(Query query, String start, String end,
			String id) {
		String page_sql = "select (select a.sourceName from t_url_tj_dict a where a.sourceid=source) sourcee ,sum(viewcount) viewcount from t_url_tj where id ="+id;
        if (start != null && !start.equals("")) {
            page_sql += " and tjdate >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            page_sql += " and tjdate <='" + end + "'";
        }
        page_sql+=" group by sourcee";
        String count_sql="select count(1) from ("+page_sql+") b";
        long total = commonDao.getCount(count_sql);
        List list = commonDao.loadMapList(page_sql);
        return new Pager<Count>(total, query.getPage(), list);
	}*/


	@Override
	public Pager findCountshoe(Query query, String start, String end, String id) {
		
		String page_sql = "select d.sourceName sourcee,d.isfirst,IFNULL(c.viewcount,0) viewcount,IFNULL(c.submitcount,0) submitcount ,d.sourceid from t_url_tj_dict d left join(select  (select a.secondName from t_url_tj_dict a where a.sourceid=source) sourcee  ,sum(viewcount) viewcount,sum(submitcount) submitcount from t_url_tj where id="+id;
        if (start != null && !start.equals("")) {
            page_sql += " and tjdate >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            page_sql += " and tjdate <='" + end + "'";
        }
        page_sql+=" group by sourcee) c on c.sourcee=d.secondName where d.isfirst='0'";
        System.out.println(page_sql);
        String count_sql="select count(1) from ("+page_sql+") b";
        long total = commonDao.getCount(count_sql);
        List list = commonDao.loadMapList(page_sql);
        return new Pager(total, query.getPage(), list);
	}

    @Override
    public Result addewm(String sourceid, String sourceName, String secondName, String url) throws Exception {
        int id = commonDao.getCount("select max(id)+1 from t_url_tj_dict");
        //String o=commonDao.getStringColumn("select secondName from t_url_tj_dict where sourceid = '"+secondName+"'");
        String sql = "insert into t_url_tj_dict (id,sourceid,sourceName,issz,iswx,istoken,url,secondName,isfirst) VALUES("+id+",'"+sourceid+"','"+sourceName+"','1','1','0','"+url+"','"+secondName+"','')";
        commonDao.update(sql);
        JSONObject json = new JSONObject();
        json.put("sourceid",sourceid);
        json.put("secondName",secondName);
        return new Result(true,json);
    }


/*	@Override
	public Pager<Count> findCountshow(Query query, String start, String end,
			String id) {
		String page_sql = "select d.sourceName sourcee,c.viewcount viewcount from t_url_tj_dict d,( select  (select a.secondName from t_url_tj_dict a where a.sourceid=source) sourcee  ,sum(viewcount) viewcount from t_url_tj where id="+id;
        if (start != null && !start.equals("")) {
            page_sql += " and tjdate >='" + start + "'";
        }
        if (end != null && !end.equals("")) {
            page_sql += " and tjdate <='" + end + "'";
        }
        page_sql+=" group by sourcee ) c where c.sourcee=d.secondName and d.isfirst='0'";
        String count_sql="select count(1) from ("+page_sql+") b";
        long total = commonDao.getCount(count_sql);
        List list = commonDao.loadMapList(page_sql);
        return new Pager<Count>(total, query.getPage(), list);
	}*/


	/*@Override
	public Pager<Count> findss(String source, Query query) {
			String ss="";
			String page_sql="select secondName from t_url_tj_dict where sourceid='"+source+"'";
			 String count_sql="select count(1) from ("+page_sql+") b";
		        long total = commonDao.getCount(count_sql);
		        List list = commonDao.loadMapList(page_sql);
		    return new Pager<Count>(total, query.getPage(), list);
		}
*/
/**
 * 根据前台传过来的sourceid查询分局的secondName
 * @param source
 * @return
 */
	private String findeSecondName(String source) {
		String secondName="";
		String page_sql="select secondName from t_url_tj_dict where sourceid='"+source+"'";
		Count count=commonDao.loadOne(page_sql, ParameterizedBeanPropertyRowMapper.newInstance(Count.class));
		secondName=count.getSecondName();
		return secondName;
	}
	


	
}
